using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;

namespace Dropthings.Widget.Widgets.FusionCharts
{
    public class DataSetHelper
    {
        private DataSet ds;

        private System.Collections.ArrayList m_FieldInfo;
        private string m_FieldList;
        private System.Collections.ArrayList GroupByFieldInfo;
        private string GroupByFieldList;

        public DataSetHelper(ref DataSet DataSet)
        {
            ds = DataSet;
        }
        public DataSetHelper()
        {
            ds = null;
        }

        private void ParseFieldList(string FieldList, bool AllowRelation)
        {
            /*
             * This code parses FieldList into FieldInfo objects  and then 
             * adds them to the m_FieldInfo private member
             * 
             * FieldList systax:  [relationname.]fieldname[ alias], ...
            */
            if (m_FieldList == FieldList) return;
            m_FieldInfo = new System.Collections.ArrayList();
            m_FieldList = FieldList;
            FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
            int i;
            for (i = 0; i <= Fields.Length - 1; i++)
            {
                Field = new FieldInfo();
                //parse FieldAlias
                FieldParts = Fields[i].Trim().Split(' ');
                switch (FieldParts.Length)
                {
                    case 1:
                        //to be set at the end of the loop
                        break;
                    case 2:
                        Field.FieldAlias = FieldParts[1];
                        break;
                    default:
                        throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");
                }
                //parse FieldName and RelationName
                FieldParts = FieldParts[0].Split('.');
                switch (FieldParts.Length)
                {
                    case 1:
                        Field.FieldName = FieldParts[0];
                        break;
                    case 2:
                        if (AllowRelation == false)
                            throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'.");
                        Field.RelationName = FieldParts[0].Trim();
                        Field.FieldName = FieldParts[1].Trim();
                        break;
                    default:
                        throw new Exception("Invalid field definition: " + Fields[i] + "'.");
                }
                if (Field.FieldAlias == null)
                    Field.FieldAlias = Field.FieldName;
                m_FieldInfo.Add(Field);
            }
        }

        private void ParseGroupByFieldList(string FieldList)
        {
            /*
            * Parses FieldList into FieldInfo objects and adds them to the GroupByFieldInfo private member
            * 
            * FieldList syntax: fieldname[ alias]|operatorname(fieldname)[ alias],...
            * 
            * Supported Operators: count,sum,max,min,first,last,avg
            */
            if (GroupByFieldList == FieldList) return;
            GroupByFieldInfo = new System.Collections.ArrayList();
            FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
            for (int i = 0; i <= Fields.Length - 1; i++)
            {
                Field = new FieldInfo();
                //Parse FieldAlias
                FieldParts = Fields[i].Trim().Split(' ');
                switch (FieldParts.Length)
                {
                    case 1:
                        //to be set at the end of the loop
                        break;
                    case 2:
                        Field.FieldAlias = FieldParts[1];
                        break;
                    default:
                        throw new ArgumentException("Too many spaces in field definition: '" + Fields[i] + "'.");
                }
                //Parse FieldName and Aggregate
                FieldParts = FieldParts[0].Split('(');
                switch (FieldParts.Length)
                {
                    case 1:
                        Field.FieldName = FieldParts[0];
                        break;
                    case 2:
                        Field.Aggregate = FieldParts[0].Trim().ToLower();    //we're doing a case-sensitive comparison later
                        Field.FieldName = FieldParts[1].Trim(' ', ')');
                        break;
                    default:
                        throw new ArgumentException("Invalid field definition: '" + Fields[i] + "'.");
                }
                if (Field.FieldAlias == null)
                {
                    if (Field.Aggregate == null)
                        Field.FieldAlias = Field.FieldName;
                    else
                        Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;
                }
                GroupByFieldInfo.Add(Field);
            }
            GroupByFieldList = FieldList;
        }

        private DataTable CreateGroupByTable(string TableName, DataTable SourceTable, string FieldList)
        {
            /*
             * Creates a table based on aggregates of fields of another table
             * 
             * RowFilter affects rows before GroupBy operation. No "Having" support
             * though this can be emulated by subsequent filtering of the table that results
             * 
             *  FieldList syntax: fieldname[ alias]|aggregatefunction(fieldname)[ alias], ...
            */
            if (FieldList == null)
            {
                throw new ArgumentException("You must specify at least one field in the field list.");
                //return CreateTable(TableName, SourceTable);
            }
            else
            {
                DataTable dt = new DataTable(TableName);
                ParseGroupByFieldList(FieldList);
                foreach (FieldInfo Field in GroupByFieldInfo)
                {
                    DataColumn dc = SourceTable.Columns[Field.FieldName];
                    if (Field.Aggregate == null)
                        dt.Columns.Add(Field.FieldAlias, dc.DataType, dc.Expression);
                    else
                        dt.Columns.Add(Field.FieldAlias, dc.DataType);
                }
                if (ds != null)
                    ds.Tables.Add(dt);
                return dt;
            }
        }

        private void InsertGroupByInto(DataTable DestTable, DataTable SourceTable, string FieldList, string GroupBy)
        {
            InsertGroupByInto(DestTable, SourceTable, FieldList, "", GroupBy, new string[] { "" });
        }

        private void InsertGroupByInto(DataTable DestTable, DataTable SourceTable, string FieldList,
    string RowFilter, string GroupBy, string[] MergeDataForColumns)
        {
            /*
             * Copies the selected rows and columns from SourceTable and inserts them into DestTable
             * FieldList has same format as CreateGroupByTable
            */
            if (FieldList == null)
                throw new ArgumentException("You must specify at least one field in the field list.");
            ParseGroupByFieldList(FieldList);	//parse field list
            ParseFieldList(GroupBy, false);			//parse field names to Group By into an arraylist
            DataRow[] Rows = SourceTable.Select(RowFilter, GroupBy);
            DataRow LastSourceRow = null, DestRow = null; bool SameRow; int RowCount = 0;
            foreach (DataRow SourceRow in Rows)
            {
                SameRow = false;
                if (LastSourceRow != null)
                {
                    SameRow = true;
                    foreach (FieldInfo Field in m_FieldInfo)
                    {
                        if (!ColumnEqual(LastSourceRow[Field.FieldName], SourceRow[Field.FieldName]))
                        {
                            SameRow = false;
                            break;
                        }
                    }
                    if (!SameRow)
                        DestTable.Rows.Add(DestRow);
                }
                if (!SameRow)
                {
                    DestRow = DestTable.NewRow();
                    RowCount = 0;
                }
                RowCount += 1;
                foreach (FieldInfo Field in GroupByFieldInfo)
                {
                    switch (Field.Aggregate)    //this test is case-sensitive
                    {
                        case null:        //implicit last
                        case "":        //implicit last
                        case "last":

                            bool go = false;
                            //checking if current field matches the fields in ColumnsToMegre
                            for (int i = 0; i < MergeDataForColumns.Length; i++)
                            {
                                if (Field.FieldAlias.Equals(MergeDataForColumns[i]))
                                {
                                    go = true;
                                    break;
                                }
                            }
                            if (go && DestRow[Field.FieldAlias].ToString().Length > 0)
                            {
                                if (!DestRow[Field.FieldAlias].ToString().Contains(SourceRow[Field.FieldName].ToString()))
                                    DestRow[Field.FieldAlias] = DestRow[Field.FieldAlias] + ", " + SourceRow[Field.FieldName];
                            }
                            else
                                DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
                            break;
                        case "first":
                            if (RowCount == 1)
                                DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
                            break;
                        case "count":
                            DestRow[Field.FieldAlias] = RowCount;
                            break;
                        case "sum":
                            DestRow[Field.FieldAlias] = Add(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
                            break;
                        case "avg":
                            DestRow[Field.FieldAlias] = Avg(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
                            break;
                        case "max":
                            DestRow[Field.FieldAlias] = Max(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
                            break;
                        case "min":
                            if (RowCount == 1)
                                DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
                            else
                                DestRow[Field.FieldAlias] = Min(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
                            break;
                    }
                }
                LastSourceRow = SourceRow;
            }
            if (DestRow != null)
                DestTable.Rows.Add(DestRow);
        }


        private FieldInfo LocateFieldInfoByName(System.Collections.ArrayList FieldList, string Name)
        {
            //Looks up a FieldInfo record based on FieldName
            foreach (FieldInfo Field in FieldList)
            {
                if (Field.FieldName == Name)
                    return Field;
            }
            return null;
        }

        private bool ColumnEqual(object a, object b)
        {
            /*
             * Compares two values to see if they are equal. Also compares DBNULL.Value.
             * 
             * Note: If your DataTable contains object fields, you must extend this
             * function to handle them in a meaningful way if you intend to group on them.
            */
            if ((a is DBNull) && (b is DBNull))
                return true;    //both are null
            if ((a is DBNull) || (b is DBNull))
                return false;    //only one is null
            return (a.Equals(b));    //value type standard comparison
            //return (a == b);    //value type standard comparison
        }

        public object[] MinAndMax(DataTable dataTable, string columnName)
        {
            object[] objs = new object[2];

            if (dataTable.Rows.Count > 0)
            {
                DataRow[] drs = dataTable.Select("1=1", columnName);
                objs[0] = drs[0][columnName].ToString();
                objs[1] = drs[drs.Length - 1][columnName].ToString();
                return objs;
            }
            return null;
        }

        public object Min(DataTable dataTable, string columnName)
        {
            if (dataTable.Rows.Count > 0)
            {
                DataRow[] drs = dataTable.Select("1=1", columnName);
                return drs[0][columnName].ToString();
            }
            return null;
        }

        public object Min(object a, object b)
        {
            //Returns MIN of two values - DBNull is less than all others
            if ((a is DBNull) || (b is DBNull))
                return DBNull.Value;

            DateTime dtA;
            double dblA = 0;

            if (DateTime.TryParse(a.ToString(), out dtA))
            {
                DateTime dtB;
                if (DateTime.TryParse(a.ToString(), out dtB))
                {
                    if (dtA < dtB)
                        return a;
                    else
                        return b;
                }
                else
                    return DBNull.Value;
            }
            else if (double.TryParse(a.ToString(), out dblA))
            {
                double dblB = 0;
                if (double.TryParse(b.ToString(), out dblB))
                {
                    if (dblA < dblB)
                        return a;
                    else
                        return b;
                }
                else
                    return DBNull.Value;
            }
            else
            {
                if (((IComparable)a).CompareTo(b) == -1)
                    return a;
                else
                    return b;
            }
        }

        public object Max(DataTable dataTable, string columnName)
        {
            if (dataTable.Rows.Count > 0)
            {
                DataRow[] drs = dataTable.Select("1=1", columnName + " DESC");
                return drs[0][columnName].ToString();
            }
            return null;
        }

        public object Max(object a, object b)
        {
            //Returns Max of two values - DBNull is less than all others
            if (a is DBNull)
                return b;
            if (b is DBNull)
                return a;


            DateTime dtA;
            double dblA = 0;

            if (DateTime.TryParse(a.ToString(), out dtA))
            {
                DateTime dtB;
                if (DateTime.TryParse(a.ToString(), out dtB))
                {
                    if (dtA > dtB)
                        return a;
                    else
                        return b;
                }
                else
                    return DBNull.Value;
            }
            else if (double.TryParse(a.ToString(), out dblA))
            {
                double dblB = 0;
                if (double.TryParse(b.ToString(), out dblB))
                {
                    if (dblA > dblB)
                        return a;
                    else
                        return b;
                }
                else
                    return DBNull.Value;
            }
            else
            {
                if (((IComparable)a).CompareTo(b) == 1)
                    return a;
                else
                    return b;
            }
        }

        public object Add(object a, object b)
        {
            //Adds two values - if one is DBNull, then returns the other
            if (a is DBNull)
                return b;
            if (b is DBNull)
                return a;
            return (Convert.ToDecimal(a) + Convert.ToDecimal(b));
        }

        public object Avg(object a, object b)
        {
            //Averages two values - if one is DBNull, then returns the other
            /*
            if (a is DBNull)
                return b;
            if (b is DBNull)
                return a;
             */
            double dblA = 0.00d;
            double dblB = 0.00d;

            double.TryParse(a.ToString(), out dblA);
            double.TryParse(b.ToString(), out dblB);

            return ((Convert.ToDouble(dblA) + Convert.ToDouble(dblB)) / 2);
        }

        public DataTable SelectGroupByInto(string TableName, DataTable SourceTable, string FieldList,
    string RowFilter, string GroupBy)
        {
            /* Usage: SelectGroupByInto("dtNewEmployee", dtEmployee, "Sum(salary) Sum_Salary, Emp_ID, Avg(salary) Avg_Salary", "dept_id=12", "Emp_ID");
             * The result datatable will be automatically sorted in asc order by column(s) provided in GroupBy parameter.              
             */
            return SelectGroupByInto(TableName, SourceTable, FieldList, RowFilter, GroupBy, new string[0]);
        }

        public DataTable SelectGroupByInto(string TableName, DataTable SourceTable, string FieldList,
    string RowFilter, string GroupBy, string[] MergeDataForColumns)
        {
            /*
             * Selects data from one DataTable to another and performs various aggregate functions
             * along the way. See InsertGroupByInto and ParseGroupByFieldList for supported aggregate functions.
             */
            DataTable dt = CreateGroupByTable(TableName, SourceTable, FieldList);
            InsertGroupByInto(dt, SourceTable, FieldList, RowFilter, GroupBy, MergeDataForColumns);
            return dt;
        }



        private class FieldInfo
        {
            public string RelationName;
            public string FieldName;	//source table field name
            public string FieldAlias;	//destination table field name
            public string Aggregate;
        }

        public DataTable RemoveDuplicates(DataTable table, List<string> keyColumns)
        {
            Dictionary<string, string> uniquenessDict = new Dictionary<string, string>(table.Rows.Count);
            StringBuilder sb = null;
            int rowIndex = 0;
            DataRow row;
            DataRowCollection rows = table.Copy().Rows;

            while (rowIndex < rows.Count)
            {
                row = rows[rowIndex];
                sb = new StringBuilder();

                foreach (string colname in keyColumns)
                {
                    sb.Append((row[colname].ToString()));
                }

                if (uniquenessDict.ContainsKey(sb.ToString()))
                {
                    rows.Remove(row);
                }
                else
                {
                    uniquenessDict.Add(sb.ToString(), string.Empty);
                    rowIndex++;
                }
            }//while end

            DataTable dtUnique = table.Clone();
            foreach (DataRow dr in rows)
            {
                dtUnique.ImportRow(dr);
            }
            return dtUnique;
        }

        private DataTable CreateJoinTable(string TableName, DataTable SourceTable, string FieldList)
        {
            /*
             * Creates a table based on fields of another table and related parent tables
             * 
             * FieldList syntax: [relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]...
            */
            if (FieldList == null)
            {
                throw new ArgumentException("You must specify at least one field in the field list.");
                //return CreateTable(TableName, SourceTable);
            }
            else
            {
                DataTable dt = new DataTable(TableName);
                ParseFieldList(FieldList, true);
                foreach (FieldInfo Field in m_FieldInfo)
                {
                    if (Field.RelationName == null)
                    {
                        DataColumn dc = SourceTable.Columns[Field.FieldName];
                        dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
                    }
                    else
                    {
                        DataColumn dc = SourceTable.ParentRelations[Field.RelationName].ParentTable.Columns[Field.FieldName];
                        dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
                    }
                }
                if (ds != null)
                    ds.Tables.Add(dt);
                return dt;
            }
        }

        private void InsertJoinInto(DataTable DestTable, DataTable SourceTable,
    string FieldList, string RowFilter, string Sort)
        {
            /*
            * Copies the selected rows and columns from SourceTable and inserts them into DestTable
            * FieldList has same format as CreatejoinTable
            */
            if (FieldList == null)
            {
                throw new ArgumentException("You must specify at least one field in the field list.");
                //InsertInto(DestTable, SourceTable, RowFilter, Sort);
            }
            else
            {
                ParseFieldList(FieldList, true);
                DataRow[] Rows = SourceTable.Select(RowFilter, Sort);
                foreach (DataRow SourceRow in Rows)
                {
                    DataRow DestRow = DestTable.NewRow();
                    foreach (FieldInfo Field in m_FieldInfo)
                    {
                        if (Field.RelationName == null)
                        {
                            DestRow[Field.FieldName] = SourceRow[Field.FieldName];
                        }
                        else
                        {
                            DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);
                            DestRow[Field.FieldName] = ParentRow[Field.FieldName];
                        }
                    }
                    DestTable.Rows.Add(DestRow);
                }
            }
        }

        public DataTable SelectJoinInto(string TableName, DataTable SourceTable, string FieldList, string RowFilter, string Sort)
        {
            /*
             * Selects sorted, filtered values from one DataTable to another.
             * Allows you to specify relationname.fieldname in the FieldList to include fields from
             *  a parent table. The Sort and Filter only apply to the base table and not to related tables.
            */
            DataTable dt = CreateJoinTable(TableName, SourceTable, FieldList);
            InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort);
            return dt;
        }
    }
}
